﻿Imports Npgsql

Module clsUIBase
    Private _frmDashboard As TicketDashBoard
    Public LoadTicketID As Integer
    Public AuthenticatedUser As clsUser
    Public Const DBConnString As String = "Server=bus1.pointpark.edu;Port=5432;User ID=Group3;Password=cmps425;Database=Group3"
    'Public Const DBConnString As String = "Server=localhost;Port=5432;User ID=ticarso;Password=cmps325;Database=ticarso"

    Public Property frmDashboard As TicketDashBoard
        Get
            Return _frmDashboard
        End Get
        Set(ByVal value As TicketDashBoard)
            _frmDashboard = value
        End Set
    End Property

    Sub Main()
        Dim LoginForm As New Login
        If LoginForm.ShowDialog() = DialogResult.OK Then
            frmDashboard = New TicketDashBoard
            Application.Run(frmDashboard)
            Environment.Exit(0)
        End If
    End Sub

    Public Function AddTechs() As DataTable
        Dim dt As New DataTable

        ' Attempt to connect to the DB to populate user properties based on username
        Try
            ' Create Npgsql connection with connection string from UIBase
            Dim conn As New NpgsqlConnection(clsUIBase.DBConnString)

            ' Open the connection
            conn.Open()

            ' Retrieve all columns from the users table pertaining to the current username from Login form
            Dim sql As String = "SELECT * FROM public.users " & _
                                "WHERE public.users.istech = TRUE"

            ' Create and initialize the Npgsql data adapter
            Dim da As New NpgsqlDataAdapter(sql, conn)

            ' Create and fill the Data set
            Dim ds As New DataSet()
            da.Fill(ds)

            ds.Tables(0).Columns.Add("assignedto", GetType(System.String), "lastname + ', ' + firstname + ' ( ID #' + userid + ' )'")

            ' Fill the data table with the only table in the dataset
            dt = ds.Tables(0)

            ' Close the connection
            conn.Close()
        Catch ex As NpgsqlException
            ' Catch/Display any npgsql exceptions
            MessageBox.Show("SQL exception: " & ex.Message)
        Catch ex As Exception
            ' Catch/Dispaly any remaining exceptions
            MessageBox.Show("There was an error: " & ex.Message)
        End Try

        Return dt
    End Function
    Public Function AddUsers() As DataTable
        Dim dt As New DataTable

        ' Attempt to connect to the DB to populate user properties based on username
        Try
            ' Create Npgsql connection with connection string from UIBase
            Dim conn As New NpgsqlConnection(clsUIBase.DBConnString)

            ' Open the connection
            conn.Open()

            ' Retrieve all columns from the users table pertaining to the current username from Login form
            Dim sql As String = "SELECT * FROM public.users"

            ' Create and initialize the Npgsql data adapter
            Dim da As New NpgsqlDataAdapter(sql, conn)

            ' Create and fill the Data set
            Dim ds As New DataSet()
            da.Fill(ds)

            ds.Tables(0).Columns.Add("assignedto", GetType(System.String), "lastname + ', ' + firstname + ' ( ID #' + userid + ' )'")

            ' Fill the data table with the only table in the dataset
            dt = ds.Tables(0)

            ' Close the connection
            conn.Close()
        Catch ex As NpgsqlException
            ' Catch/Display any npgsql exceptions
            MessageBox.Show("SQL exception: " & ex.Message)
        Catch ex As Exception
            ' Catch/Dispaly any remaining exceptions
            MessageBox.Show("There was an error: " & ex.Message)
        End Try

        Return dt
    End Function
    Public Function RetrievePriorities() As DataTable
        Dim dt As New DataTable

        ' Attempt to connect to the DB to populate user properties based on username
        Try
            ' Create Npgsql connection with connection string from UIBase
            Dim conn As New NpgsqlConnection(clsUIBase.DBConnString)

            ' Open the connection
            conn.Open()

            ' Retrieve all columns from the users table pertaining to the current username from Login form
            Dim sql As String = "SELECT * FROM public.ticketpriority"

            ' Create and initialize the Npgsql data adapter
            Dim da As New NpgsqlDataAdapter(sql, conn)

            ' Create and fill the Data set
            Dim ds As New DataSet()
            da.Fill(ds)

            ' Fill the data table with the only table in the dataset
            dt = ds.Tables(0)

            ' Close the connection
            conn.Close()
        Catch ex As NpgsqlException
            ' Catch/Display any npgsql exceptions
            MessageBox.Show("SQL exception: " & ex.Message)
        Catch ex As Exception
            ' Catch/Dispaly any remaining exceptions
            MessageBox.Show("There was an error: " & ex.Message)
        End Try

        Return dt
    End Function
    Public Function RetrieveIssueTypes() As DataTable
        Dim dt As New DataTable

        ' Attempt to connect to the DB to populate user properties based on username
        Try
            ' Create Npgsql connection with connection string from UIBase
            Dim conn As New NpgsqlConnection(clsUIBase.DBConnString)

            ' Open the connection
            conn.Open()

            ' Retrieve all columns from the users table pertaining to the current username from Login form
            Dim sql As String = "SELECT * FROM public.issuetypedef"

            ' Create and initialize the Npgsql data adapter
            Dim da As New NpgsqlDataAdapter(sql, conn)

            ' Create and fill the Data set
            Dim ds As New DataSet()
            da.Fill(ds)

            ' Fill the data table with the only table in the dataset
            dt = ds.Tables(0)

            ' Close the connection
            conn.Close()
        Catch ex As NpgsqlException
            ' Catch/Display any npgsql exceptions
            MessageBox.Show("SQL exception: " & ex.Message)
        Catch ex As Exception
            ' Catch/Dispaly any remaining exceptions
            MessageBox.Show("There was an error: " & ex.Message)
        End Try

        Return dt
    End Function

    Public Sub AddPriorityType(ByVal desc As String)
        Try
            ' Create Npgsql connection with connection string from UIBase
            Dim conn As New NpgsqlConnection(clsUIBase.DBConnString)

            ' Open the connection
            conn.Open()

            ' Retrieve all columns from the users table pertaining to the current username from Login form
            Dim sql As String = "INSERT INTO public.ticketpriority (prioritydesc) VALUES ('" & desc & "')"

            ' Create and initialize the Npgsql data command
            Dim da As New NpgsqlCommand(sql, conn)

            ' Execute the query
            da.ExecuteNonQuery()

            ' Close the connection
            conn.Close()
        Catch ex As NpgsqlException
            ' Catch/Display any npgsql exceptions
            MessageBox.Show("SQL exception: " & ex.Message)
        Catch ex As Exception
            ' Catch/Dispaly any remaining exceptions
            MessageBox.Show("There was an error: " & ex.Message)
        End Try
    End Sub

    Public Sub DeletePriorityType(ByVal id As Integer)
        Try
            ' Create Npgsql connection with connection string from UIBase
            Dim conn As New NpgsqlConnection(clsUIBase.DBConnString)

            ' Open the connection
            conn.Open()

            ' Retrieve all columns from the users table pertaining to the current username from Login form
            Dim sql As String = "DELETE FROM public.ticketpriority WHERE priorityid = '" & id & "'"

            ' Create and initialize the Npgsql data command
            Dim da As New NpgsqlCommand(sql, conn)

            ' Execute the query
            da.ExecuteNonQuery()

            ' Close the connection
            conn.Close()
        Catch ex As NpgsqlException
            ' Catch/Display any npgsql exceptions
            MessageBox.Show("SQL exception: " & ex.Message)
        Catch ex As Exception
            ' Catch/Dispaly any remaining exceptions
            MessageBox.Show("There was an error: " & ex.Message)
        End Try
    End Sub

    Public Sub AddIssueType(ByVal desc As String)
        Try
            ' Create Npgsql connection with connection string from UIBase
            Dim conn As New NpgsqlConnection(clsUIBase.DBConnString)

            ' Open the connection
            conn.Open()

            ' Retrieve all columns from the users table pertaining to the current username from Login form
            Dim sql As String = "INSERT INTO public.issuetypedef (issuetypedesc) VALUES ('" & desc & "')"

            ' Create and initialize the Npgsql data command
            Dim da As New NpgsqlCommand(sql, conn)

            ' Execute the query
            da.ExecuteNonQuery()

            ' Close the connection
            conn.Close()
        Catch ex As NpgsqlException
            ' Catch/Display any npgsql exceptions
            MessageBox.Show("SQL exception: " & ex.Message)
        Catch ex As Exception
            ' Catch/Dispaly any remaining exceptions
            MessageBox.Show("There was an error: " & ex.Message)
        End Try
    End Sub

    Public Sub DeleteIssueType(ByVal id As Integer)
        Try
            ' Create Npgsql connection with connection string from UIBase
            Dim conn As New NpgsqlConnection(clsUIBase.DBConnString)

            ' Open the connection
            conn.Open()

            ' Retrieve all columns from the users table pertaining to the current username from Login form
            Dim sql As String = "DELETE FROM public.issuetypedef WHERE issuetypeid = '" & id & "'"

            ' Create and initialize the Npgsql data command
            Dim da As New NpgsqlCommand(sql, conn)

            ' Execute the query
            da.ExecuteNonQuery()

            ' Close the connection
            conn.Close()
        Catch ex As NpgsqlException
            ' Catch/Display any npgsql exceptions
            MessageBox.Show("SQL exception: " & ex.Message)
        Catch ex As Exception
            ' Catch/Dispaly any remaining exceptions
            MessageBox.Show("There was an error: " & ex.Message)
        End Try
    End Sub
End Module
